const express = require("express");
const multer = require("multer");
const router = express.Router();
const path = require("path");
const { generateUniqueId } = require("../public/javascripts/index");
const db = require("../db");
const ExcelJS = require("exceljs");

router.post("/add", (req, res) => {
  const unitInfo = req.body;
  //对客户端的数据进行校验
  if (!unitInfo.warehouseName) {
    return res.send({
      success: false,
      message: "库名称不能为空",
    });
  }
  // 执行定义好的登录函数
  const warehouseInfo = req.body;
  let sql = "select * from selection_warehouse where warehouseName=?";
  db.query(sql, [warehouseInfo.warehouseName], (error, result) => {
    if (error) {
      return res.cc(error);
    }
    if (result.length > 0) {
      return res.cc("库名称已被占用!");
    }
    // 定义 SQL 语句
    let sql1 = "insert into selection_warehouse set ?";
    db.query(
      sql1,
      {
        warehouseId: generateUniqueId(),
        warehouseName: warehouseInfo.warehouseName,
        createdTime: Date.now(),
        remark: warehouseInfo.remark,
      },
      (error, result) => {
        if (error) return res.cc(error);
        // 判断影响行数是否为 1
        if (result.affectedRows !== 1) return res.cc("新增库失败！");
        return res.cc("新增库成功", true);
      }
    );
  });
});

router.delete("/delete", (req, res) => {
  const unitInfo = req.body;
  //对客户端的数据进行校验
  if (!unitInfo.warehouseId) {
    return res.send({
      success: false,
      message: "库编码不能为空",
    });
  }
  // 执行定义好的登录函数
  const warehouseInfo = req.body;
  let sql = "select * from selection_warehouse where warehouseId=?";
  db.query(sql, [warehouseInfo.warehouseId], (error, result) => {
    if (error) {
      return res.cc(error);
    }
    if (result.length === 0) {
      return res.cc("未找到该库!");
    }
    // 定义 SQL 语句
    const sql1 = "delete from selection_warehouse where warehouseId=?";
    // 调用 db.query() 执行 sql 语句
    db.query(sql1, warehouseInfo.warehouseId, (error, result) => {
      if (error) return res.cc(error);
      // 判断影响行数是否为 1
      if (result.affectedRows !== 1) return res.cc("删除库失败！");
      return res.cc("删除库成功", true);
    });
  });
});

router.put("/update", (req, res) => {
  const unitInfo = req.body;
  //对客户端的数据进行校验
  if (!unitInfo.warehouseId) {
    return res.send({
      success: false,
      message: "库id不能为空",
    });
  }
  // 执行定义好的登录函数
  const warehouseInfo = req.body;
  let sql = "select * from selection_warehouse where warehouseId=?";
  db.query(sql, [warehouseInfo.warehouseId], (error, result) => {
    if (error) {
      return res.cc(error);
    }
    if (result.length === 0) {
      return res.cc("未找到该库!");
    }
    // 更新库信息
    const updateSQL =
      "UPDATE selection_warehouse SET warehouseName=?, remark=? WHERE warehouseId=?";
    const updateValues = [
      warehouseInfo.warehouseName,
      warehouseInfo.remark,
      warehouseInfo.warehouseId,
    ];

    db.query(updateSQL, updateValues, (error, result) => {
      if (error) {
        return res.cc(error);
      }

      if (result.affectedRows !== 1) {
        return res.cc("修改库失败！");
      }

      return res.cc("修改库成功", true);
    });
  });
});

router.post("/list", (req, res) => {
  const { page = 1, limit = 10, warehouseName = "" } = req.body;
  const offset = (page - 1) * limit;
  const sqlCount = "SELECT COUNT(*) AS total FROM selection_warehouse";
  let sql;
  let params;

  db.query(sqlCount, (error, countResults) => {
    if (error) {
      return res.cc(error);
    }

    const total = countResults[0].total;

    if (warehouseName) {
      sql = `SELECT * FROM selection_warehouse WHERE warehouseName LIKE ? LIMIT ? OFFSET ?`;
      params = [`%${warehouseName}%`, limit, offset];
    } else {
      sql = `SELECT * FROM selection_warehouse LIMIT ? OFFSET ?`;
      params = [limit, offset];
    }

    db.query(sql, params, (error, results) => {
      if (error) {
        return res.cc(error);
      }

      return res.cc("获取记录成功", true, results, {
        total,
        page,
        limit,
      });
    });
  });
});

router.get("/excel", async (req, res) => {
  try {
    // 创建一个新的 Excel 工作簿
    const workbook = new ExcelJS.Workbook();
    const worksheet = workbook.addWorksheet("商家模板");

    worksheet.columns = [
      { header: "商家名称", key: "businessName", width: 30 },
      { header: "联系人", key: "contacts", width: 20 },
      { header: "联系方式", key: "tel", width: 20 },
      { header: "详细地址", key: "address", width: 30 },
      { header: "备注", key: "contacts", width: 20 },
    ];

    worksheet.getRow(1).eachCell({ includeEmpty: true }, (cell, colNumber) => {
      cell.fill = {
        type: "pattern",
        pattern: "solid",
        fgColor: { argb: "FF79bbff" },
      };
      cell.font = {
        size: 15,
        color: { argb: "0000000" }, // 白色
      };
    });
    // 设置数据行居左对齐
    worksheet.eachRow({ includeEmpty: true }, (row, rowNumber) => {
      row.eachCell({ includeEmpty: true }, (cell, colNumber) => {
        cell.alignment = { horizontal: "left", vertical: "middle" };
      });
      row.height = 30;
    });

    // 生成 Excel 文件并发送给客户端
    res.setHeader(
      "Content-Type",
      "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"
    );
    res.setHeader(
      "Content-Disposition",
      'attachment; filename="business-example.xlsx"'
    );
    await workbook.xlsx.write(res);
    res.end();
    // });
  } catch (err) {
    return res.cc(err);
  }
});

// 使用 multer 直接存储在内存中，而不是写入磁盘
const upload = multer();

// 处理文件上传的路由
router.post("/uploadExcel", upload.single("file"), (req, res) => {
  if (!req.file) {
    return res.cc("未选择文件上传。");
  }
  if (!req.body.warehouseId || !req.body.warehouseName) {
    return res.cc("缺少必要参数");
  }
  // 从内存中读取 Excel 文件内容
  const workbook = new ExcelJS.Workbook();
  workbook.xlsx
    .load(req.file.buffer)
    .then(() => {
      // 假设模板中只有一个工作表，可以直接获取第一个工作表
      const worksheet = workbook.getWorksheet(1);

      // 假设第一行是标题行，读取标题行的每一列
      const headers = worksheet.getRow(1).values;

      // headers 是一个数组，包含每列的标题
      // 遍历 headers，找到每列对应的 key
      const columns = [
        { key: "businessName", columnIndex: headers.indexOf("商家名称") },
        { key: "contacts", columnIndex: headers.indexOf("联系人") },
        { key: "tel", columnIndex: headers.indexOf("联系方式") },
        { key: "address", columnIndex: headers.indexOf("详细地址") },
        { key: "remark", columnIndex: headers.indexOf("备注") },
      ];

      // 读取数据行，从第二行开始
      let list = [];
      for (let i = 2; i <= worksheet.rowCount; i++) {
        const row = worksheet.getRow(i);

        // 根据 columns 中的配置，读取每列数据
        const rowData = {};
        columns.forEach((col) => {
          if (col.columnIndex > 0) {
            rowData[col.key] = row.getCell(col.columnIndex).value;
          }
        });
        rowData.businessId = generateUniqueId();
        rowData.warehouseName = req.body.warehouseName;
        rowData.warehouseId = req.body.warehouseId;
        rowData.createdTime = Date.now();

        list.push(rowData);
      }

      // 批量插入数据
      let sql =
        "INSERT INTO selection_business (businessId, businessName, warehouseId, warehouseName, address, contacts, tel, createdTime, remark, addType) VALUES ?";
      let values = list.map((item) => [
        item.businessId,
        item.businessName,
        item.warehouseId,
        item.warehouseName,
        item.address,
        item.contacts,
        item.tel,
        item.createdTime,
        item.remark,
        "批量导入",
      ]);

      db.query(sql, [values], (error, result) => {
        if (error) {
          console.error("Error inserting into database:", error);
          return res.cc("导入商家失败！");
        }
        console.log("Inserted rows:", result.affectedRows);
        return res.cc("导入成功，请前往商家管理查看！", true);
      });
    })
    .catch((err) => {
      console.error("Error parsing Excel file:", err);
      return res.cc("解析 Excel 文件时出错");
    });
});

module.exports = router;
